﻿using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.IO;

namespace ExcelDemo.Helpers
{
    public class NpoiHelper
    {
        /// <summary>
        /// 数据流的类型
        /// </summary>
        public string ContentType { get; set; }

        private IWorkbook wk;
        private ISheet sheet;
        private ICellStyle defaultStyle;
        private ICellStyle titleStyle;
        private ICellStyle dateTimeStyle;
        private ICellStyle dateStyle;
        private ICellStyle timeStyle;
        private ICellStyle IntegerStyle;
        private ICellStyle oneDecimaPlacesStyle;
        private ICellStyle twoDecimaPlacesStyle;
        private ICellStyle threeDecimaPlacesStyle;

        public NpoiHelper()
        {
            ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            wk = new NPOI.XSSF.UserModel.XSSFWorkbook();
            sheet = wk.CreateSheet("Sheet1");
            defaultStyle = GetCellStyle(wk);
            titleStyle = GetCellStyle(wk, NpoiStyleType.Title);
            dateTimeStyle = GetCellStyle(wk, NpoiStyleType.DateTime);
            dateStyle = GetCellStyle(wk, NpoiStyleType.Date);
            timeStyle = GetCellStyle(wk, NpoiStyleType.Time);
            IntegerStyle = GetCellStyle(wk, NpoiStyleType.Integer);
            oneDecimaPlacesStyle = GetCellStyle(wk, NpoiStyleType.OneDecimalPlaces);
            twoDecimaPlacesStyle = GetCellStyle(wk, NpoiStyleType.TwoDecimalPlaces);
            threeDecimaPlacesStyle = GetCellStyle(wk, NpoiStyleType.ThreeDecimalPlaces);
        }

        /// <summary>
        /// 获取样式
        /// </summary>
        /// <param name="wk"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        private ICellStyle GetCellStyle(IWorkbook wk, NpoiStyleType type = NpoiStyleType.Default)
        {
            ICellStyle cellStyle = wk.CreateCellStyle();
            IDataFormat dataformat = wk.CreateDataFormat();
            //边框
            cellStyle.BorderTop = BorderStyle.Thin;//上
            cellStyle.BorderBottom = BorderStyle.Thin;//下
            cellStyle.BorderLeft = BorderStyle.Thin;//左
            cellStyle.BorderRight = BorderStyle.Thin;//右
            switch (type)
            {
                case NpoiStyleType.Title://标题
                    //居中
                    cellStyle.VerticalAlignment = VerticalAlignment.Center;
                    cellStyle.Alignment = HorizontalAlignment.Center;
                    break;

                case NpoiStyleType.DateTime:
                    cellStyle.DataFormat = dataformat.GetFormat("yyyy-mm-dd hh:mm:ss;@");
                    break;

                case NpoiStyleType.Date:
                    cellStyle.DataFormat = dataformat.GetFormat("yyyy-mm-dd;@");
                    break;

                case NpoiStyleType.Time:
                    cellStyle.DataFormat = dataformat.GetFormat("hh:mm:ss;@");
                    break;

                case NpoiStyleType.Integer://正整数
                    cellStyle.DataFormat = dataformat.GetFormat("0_ ");
                    break;

                case NpoiStyleType.OneDecimalPlaces://1位小数数值
                    cellStyle.DataFormat = dataformat.GetFormat("0.0_ ");
                    break;

                case NpoiStyleType.TwoDecimalPlaces://2位小数数值
                    cellStyle.DataFormat = dataformat.GetFormat("0.00_ ");
                    break;

                case NpoiStyleType.ThreeDecimalPlaces://3位小数数值
                    cellStyle.DataFormat = dataformat.GetFormat("0.000_ ");
                    break;

                default:
                    break;
            }
            return cellStyle;
        }

        /// <summary>
        /// 添加单元格
        /// </summary>
        /// <param name="rowIndex">行坐标，0开始</param>
        /// <param name="colIndex">列坐标，0开始</param>
        /// <param name="value">填充的值</param>
        /// <param name="styleType">0、默认 1、标题 2、2位小数 3、整数</param>
        /// <param name="mergeColumns">合并的几列</param>
        /// <param name="mergeRow">合并的几行</param>
        public void AddCell(int rowIndex, int colIndex, object value, NpoiStyleType? styleType = null, int? mergeColumns = null, int? mergeRow = null)
        {
            ICell cell;
            //行是否存在
            if (sheet.GetRow(rowIndex) == null)
            {
                cell = sheet.CreateRow(rowIndex).CreateCell(colIndex);
            }
            else
            {
                cell = sheet.GetRow(rowIndex).CreateCell(colIndex);
            }

            cell.CellStyle = defaultStyle;//添加默认样式
            double num = 0;
            switch (value?.GetType().ToString())
            {
                case "System.String"://字符串类型
                    cell.SetCellValue(value.ToString());
                    break;

                case "System.DateTime"://日期类型
                    cell.SetCellValue((DateTime)value);
                    cell.CellStyle = dateTimeStyle;//添加整型样式
                    break;

                case "System.Boolean"://布尔型
                    cell.SetCellValue((bool)value);
                    break;

                case "System.Int16"://整型
                case "System.Int32":
                case "System.Int64":
                case "System.Byte":
                    double.TryParse(value.ToString(), out num);
                    cell.SetCellValue(num);
                    cell.CellStyle = IntegerStyle;//添加整型样式
                    break;

                case "System.Decimal"://浮点型
                case "System.Double":
                    double.TryParse(value.ToString(), out num);
                    cell.SetCellValue(num);
                    cell.CellStyle = twoDecimaPlacesStyle;//添加浮点样式
                    break;

                case "System.DBNull"://空值处理
                default:
                    cell.SetCellValue("");
                    break;
            }
            //添加自定义样式
            if (styleType.HasValue)
            {
                switch (styleType)
                {
                    case NpoiStyleType.Title://标题
                        cell.CellStyle = titleStyle;
                        break;

                    case NpoiStyleType.DateTime://日期时间
                        cell.CellStyle = dateTimeStyle;
                        break;

                    case NpoiStyleType.Date://日期
                        cell.CellStyle = dateStyle;
                        break;

                    case NpoiStyleType.Time://时间
                        cell.CellStyle = timeStyle;
                        break;

                    case NpoiStyleType.Integer://整数
                        cell.CellStyle = IntegerStyle;
                        break;

                    case NpoiStyleType.OneDecimalPlaces://1位小数数值
                        cell.CellStyle = oneDecimaPlacesStyle;
                        break;

                    case NpoiStyleType.TwoDecimalPlaces://2位小数数值
                        cell.CellStyle = twoDecimaPlacesStyle;
                        break;

                    case NpoiStyleType.ThreeDecimalPlaces://3位小数数值
                        cell.CellStyle = threeDecimaPlacesStyle;
                        break;

                    default:
                        cell.CellStyle = defaultStyle;
                        break;
                }
            }
            //合并单元格
            if (mergeColumns.HasValue || mergeRow.HasValue)
            {
                int mRow = mergeRow.GetValueOrDefault(0);
                int mColumns = mergeColumns.GetValueOrDefault(0);
                if (mRow == 0 && mColumns == 0)
                {
                    return;
                }
                //填充合并的单元格
                for (int x = 0; x <= mColumns; x++)
                {
                    for (int y = 0; y <= mRow; y++)
                    {
                        if (x != 0 || y != 0)
                        {
                            AddCell(rowIndex + y, colIndex + x, "");
                        }
                    }
                }
                sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + mRow, colIndex, colIndex + mColumns));
                //合并后居中
                cell.CellStyle.VerticalAlignment = VerticalAlignment.Center;
                cell.CellStyle.Alignment = HorizontalAlignment.CenterSelection;
            }
        }

        /// <summary>
        /// 添加下拉框验证，从第二行开始添加
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="rowIndex">开始行</param>
        /// <param name="colIndex">列</param>
        /// <param name="listOfValues">下拉框值</param>
        public void AddSelectCol(int rowIndex, int colIndex, string[] listOfValues)
        {
            //获得一个数据验证Helper
            XSSFDataValidationHelper helper = new XSSFDataValidationHelper((XSSFSheet)sheet);
            //区域范围，xls最大行65535，xlsx最大行1048575
            CellRangeAddressList regions = new CellRangeAddressList(rowIndex, 1048575, colIndex, colIndex);
            //下拉序列值（总字数超过256个会失效）
            IDataValidationConstraint constraint = helper.CreateExplicitListConstraint(listOfValues);
            //创建约束
            IDataValidation validation = helper.CreateValidation(constraint, regions);
            validation.ShowErrorBox = true;//显示错误提示
            validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!");//不符合约束时的提示
            sheet.AddValidationData(validation);//添加进去
        }

        /// <summary>
        ///  自动宽度
        /// </summary>
        public void AutoWidth()
        {
            var lastRowNum = sheet.LastRowNum;
            if (lastRowNum > 50) lastRowNum = 50;//只比对前几行宽度

            short maxColNum = 0;
            for (int rowNum = 0; rowNum <= lastRowNum; rowNum++)
            {
                var row = sheet.GetRow(rowNum);
                if (row != null)
                {
                    if (row.LastCellNum > maxColNum)
                    {
                        maxColNum = row.LastCellNum;
                    }
                }
            }

            for (int colNum = 0; colNum < maxColNum; colNum++)
            {
                int columnWidth = sheet.GetColumnWidth(colNum) / 256;//获取当前列宽度
                for (int rowNum = 0; rowNum <= lastRowNum; rowNum++)
                {
                    var row = sheet.GetRow(rowNum);
                    if (row != null)
                    {
                        ICell cell = row.GetCell(colNum);
                        if (cell != null)
                        {
                            int contextLength = System.Text.Encoding.UTF8.GetBytes(cell.ToString()).Length;//获取当前单元格的内容宽度
                            columnWidth = columnWidth < contextLength ? contextLength : columnWidth;
                        }
                    }
                }
                if (columnWidth > 50)
                {
                    columnWidth = 50;
                }
                sheet.SetColumnWidth(colNum, (columnWidth + 1) * 200);
            }
        }

        /// <summary>
        /// 获取excel字节数组
        /// </summary>
        /// <param name="autoWidth">是否自动宽度</param>
        /// <returns></returns>
        public byte[] GetByte(bool autoWidth = true)
        {
            if (autoWidth)
            {
                AutoWidth();
            }
            MemoryStream ms = new MemoryStream();
            wk.Write(ms);
            wk.Close();
            return ms.ToArray();
        }

        /// <summary>
        /// 获取单元格值
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        public static string GetCellValue(ICell cell)
        {
            if (cell == null)
            {
                return string.Empty;
            }
            switch (cell.CellType)
            {
                case CellType.Numeric:
                    return cell.NumericCellValue.ToString();

                case CellType.String:
                    return cell.StringCellValue;

                case CellType.Boolean:
                    return cell.BooleanCellValue ? "是" : "否";

                case CellType.Formula://公式
                case CellType.Blank://空值
                case CellType.Error://错误类型
                case CellType.Unknown://未知类型
                default:
                    return string.Empty;
            }
        }
    }

    public enum NpoiStyleType
    {
        /// <summary>
        /// 默认
        /// </summary>
        Default = -1,

        /// <summary>
        /// 日期时间 yyyy-mm-dd hh:mm:ss;@
        /// </summary>
        DateTime = -2,

        /// <summary>
        /// 日期 yyyy-mm-dd;@
        /// </summary>
        Date = -3,

        /// <summary>
        /// 时间 hh:mm:ss;@
        /// </summary>
        Time = -4,

        /// <summary>
        /// 标题
        /// </summary>
        Title = 99,

        /// <summary>
        /// 整数
        /// </summary>
        Integer = 0,

        /// <summary>
        /// 1位小数
        /// </summary>
        OneDecimalPlaces = 1,

        /// <summary>
        /// 2位小数
        /// </summary>
        TwoDecimalPlaces = 2,

        /// <summary>
        /// 3位小数
        /// </summary>
        ThreeDecimalPlaces = 3,
    }
}